package com.amado.easyui2.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.amado.easyui2.dao.CoachStaffDao;
import com.amado.easyui2.domain.CoachStaffEntity;
import com.amado.easyui2.util.DBConnection;
import com.amado.easyui2.vo.Page;

public class CoachStaffDaoImpl implements CoachStaffDao {

	@Override
	public void add(CoachStaffEntity cn) {
		Connection conn = JDBC.getConn();
		String sql = "insert into t_coach_staff (learn_card_number,driving_school_name,coach_name,coach_number,coach_id_card,coach_sex,coach_date_birth,nation,quasi_driving_type,must_teach_models,place_of_domicile,phone,reference_status,return_message,popedom,province,city,contact_address,must_teach_classes,learn_history,social_security_number,working_state,create_time,update_time,create_name,update_name)value(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			int i = 1;
			ps = conn.prepareStatement(sql);
			ps.setString(i++, cn.getLearnCardNumber());
			ps.setString(i++, cn.getDrivingSchoolName());
			ps.setString(i++, cn.getCoachName());
			ps.setString(i++, cn.getCoachNumber());
			ps.setString(i++, cn.getCoachIdCard());
			ps.setString(i++, cn.getCoachSex());
			ps.setString(i++, cn.getCoachDateBirth());
			ps.setString(i++, cn.getNation());
			ps.setString(i++, cn.getQuasiDrivingType());
			ps.setString(i++, cn.getMustTeachModels());
			ps.setString(i++, cn.getPlaceOfDomicile());
			ps.setString(i++, cn.getPhone());
			ps.setString(i++, cn.getReferenceStatus());
			ps.setString(i++, cn.getReturnMessage());
			ps.setString(i++, cn.getPopedom());
			ps.setString(i++, cn.getProvince());
			ps.setString(i++, cn.getCity());
			ps.setString(i++, cn.getContactAddress());
			ps.setString(i++, cn.getMustTeachClasses());
			ps.setString(i++, cn.getLearnHistory());
			ps.setString(i++, cn.getSocialSecurityNumber());
			
			
//		ps.setDate(23,cn.getDrivingLicenceStatsTime());
//		ps.setDate(24, cn.getPactStatsTime());
//		ps.setDate(25, cn.getPactEndTime());
			ps.setString(i++, cn.getWorkingState());
//			ps.setString(i++, cn.getLearnCardNumber());
			ps.setTimestamp(i++, new java.sql.Timestamp(System.currentTimeMillis()));
			ps.setTimestamp(i++, new java.sql.Timestamp(System.currentTimeMillis()));
			ps.setString(i++, cn.getCreateName());
			ps.setString(i++, cn.getUpdateName());
//		ps.setDate(27, cn.getCertificateTime());
//		ps.setDate(28, cn.getValidTime());
			ps.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBC.close(null, ps, conn);
	}

	@Override
	public void delete(List<Long> ids) {
		if(ids==null || ids.isEmpty()) {
			return;
		}
		Connection conn = JDBC.getConn();
		StringBuilder sql =new StringBuilder("update t_coach_staff set delete_logo=? where id in (");
		for(int i=0;i<ids.size();i++) {
			if(i==0) {
				sql.append("?");
			}else {
				sql.append(",?");
			}
		}
		sql.append(")");
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, "Y");
			for(int i=0;i<ids.size();i++) {
				ps.setLong(i+2, ids.get(i));
			}
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBC.close(null, ps, conn);

	}

	@Override
	public int update(CoachStaffEntity id) {
		Connection conn = JDBC.getConn();
		String sql = "update t_coach_staff set driving_school_name=?,coach_name=?,coach_number=?,coach_id_card=?,coach_sex=?,coach_date_birth=?,nation=?,quasi_driving_type=?,must_teach_models=?,place_of_domicile=?,phone=?,reference_status=?,return_message=?,popedom=?,province=?,city=?,contact_address=?,must_teach_classes=?,learn_history=?,social_security_number=?,working_state=?,create_time=?,update_time=?,create_name=?,update_name=?  where id=?";
		PreparedStatement ps = null;
		try {
			int i = 1;
			ps = conn.prepareStatement(sql);
			ps.setString(i++, id.getDrivingSchoolName());
			ps.setString(i++, id.getCoachName());
			ps.setString(i++, id.getCoachNumber());
			ps.setString(i++, id.getCoachIdCard());
			ps.setString(i++, id.getCoachSex());
			ps.setString(i++, id.getCoachDateBirth());
			ps.setString(i++, id.getNation());
			ps.setString(i++, id.getQuasiDrivingType());
			ps.setString(i++, id.getMustTeachModels());
			ps.setString(i++, id.getPlaceOfDomicile());
			ps.setString(i++, id.getPhone());
			ps.setString(i++, id.getReferenceStatus());
			ps.setString(i++, id.getReturnMessage());
			ps.setString(i++, id.getPopedom());
			ps.setString(i++, id.getProvince());
			ps.setString(i++, id.getCity());
			ps.setString(i++, id.getContactAddress());
			ps.setString(i++, id.getMustTeachClasses());
			ps.setString(i++, id.getLearnHistory());
			ps.setString(i++, id.getSocialSecurityNumber());
//			ps.setDate(i++, id.getDrivingLicenceStatsTime());
//			ps.setDate(i++, id.getPactStatsTime());
//			ps.setDate(i++, id.getPactEndTime());
			ps.setString(i++, id.getWorkingState());
			
			ps.setTimestamp(i++, new java.sql.Timestamp(System.currentTimeMillis()));
			ps.setTimestamp(i++, new java.sql.Timestamp(System.currentTimeMillis()));
			ps.setString(i++, id.getCreateName());
			ps.setString(i++, id.getUpdateName());
			ps.setLong(i++, id.getId());
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBC.close(null, ps, conn);
		return 0;
	}

	@Override
	public List<CoachStaffEntity> listAll() {
		Connection conn = JDBC.getConn();
		String sql = "select * from t_coach_staff";
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<CoachStaffEntity> coach = new ArrayList();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				int i = 1;
				Long id = rs.getLong(i++);
				String drivingSchoolName = rs.getString("driving_school_name");
				
				String coachName = rs.getString("coach_name");
				String coachNumber = rs.getString("coach_number");
				String coachIdCard = rs.getString("coach_id_card");
				String coachSex = rs.getString("coach_sex");
				String coachDateBirth = rs.getString("coach_date_birth");
				String nation = rs.getString("nation");
				String quasiDrivingType = rs.getString("quasi_driving_type");
				String mustTeachModels = rs.getString("must_teach_models");
				String placeOfDomicile = rs.getString("place_of_domicile");
				String phone = rs.getString("phone");
				String referenceStatus = rs.getString("reference_status");
				String returnMessage = rs.getString("return_message");
				String popedom = rs.getString("popedom");
				String province = rs.getString("province");
				String city = rs.getString("city");
				String contactAddress = rs.getString("contact_address");
				String mustTeachClasses = rs.getString("must_teach_classes");
				String learnHistory = rs.getString("learn_history");
				String socialSecurityNumber = rs.getString("social_security_number");
				Date drivingLicenceStatsTime = rs.getDate("driving_licence_stats_time");
				Date pactEndTime = rs.getDate("pact_end_time");
				Date pactStatsTime = rs.getDate("pact_stats_time");
				String workingState = rs.getString("working_state");
				Date certificateTime = rs.getDate("certificate_time");
				Date validTime = rs.getDate("valid_time");
				String deleteLogo = rs.getString("delete_logo");
				String learnCardNumber = rs.getString("learn_card_number");
				Date createTime=rs.getTimestamp("create_time");
				Date updateTime=rs.getTimestamp("update_time");
				String createName=rs.getString("create_name");
				String updateName=rs.getString("update_name");
				coach.add(new CoachStaffEntity(id, drivingSchoolName, coachName, coachNumber, coachIdCard, coachSex,
						coachDateBirth, nation, quasiDrivingType, mustTeachModels, placeOfDomicile, phone,
						referenceStatus, returnMessage, popedom, province, city, contactAddress, mustTeachClasses,
						learnHistory, socialSecurityNumber, drivingLicenceStatsTime, pactStatsTime, pactEndTime,
						workingState, certificateTime, validTime,deleteLogo, learnCardNumber,createTime,updateTime,createName,updateName));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return coach;
	}

	@Override
	public List<CoachStaffEntity> login(CoachStaffEntity schoolName) {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM t_coach_staff where coach_name=? and coach_id_card=? ";
		List<CoachStaffEntity> list = new ArrayList<CoachStaffEntity>();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				int i = 1;
				String coachName = rs.getString(i++);
				String coachIdCard = rs.getString(i++);

				list.add(new CoachStaffEntity(coachName, coachIdCard));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<CoachStaffEntity> listAll(String cn, String cIC) {
		Connection conn = JDBC.getConn();
		String sql = "select * from t_coach_staff where delete_logo='N'";
		PreparedStatement ps = null;
		ResultSet rs = null;
		if (cn != null && cn.trim() != "") {
			sql = sql + " and coach_name like ?";
		}
		if (cIC != null && cIC.trim() != "") {
			sql = sql + " and coach_id_card =?";
		}
		
		System.out.println("sql" + sql);
		List<CoachStaffEntity> list = new ArrayList<CoachStaffEntity>();
		try {
			ps = conn.prepareStatement(sql);
			int j = 1;
			if (cn != null && cn.trim() != "") {
				ps.setString(j++, cn);
			}
			if (cIC != null && cIC.trim() != "") {
				ps.setString(j++, "%" + cIC + "%");
			}
			if (cn != null && cn.trim() != "") {
				ps.setString(j++, "%" + cn + "%");
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				int i = 1;
//				 private Date drivingLicenceStatsTime; 
//				   private Date pactStatsTime;     
//				   private Date pactEndTime;       
//				   private String workingState;       
//				   private Date certificateTime;     
//				   private Date validTime;
				Long id = rs.getLong(i++);
				String drivingSchoolName = rs.getString(i++);

				String coachName = rs.getString(i++);
				String coachNumber = rs.getString(i++);
				String coachIdCard = rs.getString(i++);
				String coachSex = rs.getString(i++);
				String coachDateBirth = rs.getString(i++);
				String nation = rs.getString(i++);
				String quasiDrivingType = rs.getString(i++);
				String mustTeachModels = rs.getString(i++);
				String placeOfDomicile = rs.getString(i++);
				String phone = rs.getString(i++);
				String referenceStatus = rs.getString(i++);
				String returnMessage = rs.getString(i++);
				String popedom = rs.getString(i++);
				String province = rs.getString(i++);
				String city = rs.getString(i++);
				String contactAddress = rs.getString(i++);
				String mustTeachClasses = rs.getString(i++);
				String learnHistory = rs.getString(i++);
				String socialSecurityNumber = rs.getString(i++);
				Date drivingLicenceStatsTime = rs.getDate(i++);
				Date pactStatsTime = rs.getDate(i++);
				Date pactEndTime = rs.getDate(i++);
				String workingState = rs.getString(i++);
				Date certificateTime = rs.getDate(i++);
				Date validTime = rs.getDate(i++);
				String learnCardNumber = rs.getString(i++);
				String deleteLogo = rs.getString(i++);
				Date createTime=rs.getTimestamp(i++);
				Date updateTime=rs.getTimestamp(i++);
				String createName=rs.getString(i++);
				String updateName=rs.getString(i++);
				list.add(new CoachStaffEntity(id, drivingSchoolName, coachName, coachNumber, coachIdCard, coachSex,
						coachDateBirth, nation, quasiDrivingType, mustTeachModels, placeOfDomicile, phone,
						referenceStatus, returnMessage, popedom, province, city, contactAddress, mustTeachClasses,
						learnHistory, socialSecurityNumber, drivingLicenceStatsTime, pactStatsTime, pactEndTime,
						workingState, certificateTime, validTime,deleteLogo,learnCardNumber,createTime,updateTime,createName,updateName));
			}
		} catch (SQLException e) {

			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBC.close(rs, ps, conn);
		return list;
	}

	@Override
	public int listAllCount(String cn, String cIC) {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT count(1) FROM t_coach_staff where delete_logo='N'";
		if (cn != null && cn.trim() != "") {
			sql = sql + " and coach_name like ?";
		}
		if (cIC != null && cIC.trim() != "") {
			sql = sql + " and coach_id_card =?";
		}
		System.out.println("sql" + sql);
		List<CoachStaffEntity> list = new ArrayList<CoachStaffEntity>();
		try {
			ps = conn.prepareStatement(sql);
			int j = 1;
			if (cn != null && cn.trim() != "") {
				ps.setString(j++, cn);
			}
			if (cIC != null && cIC.trim() != "") {
				ps.setString(j++, "%" + cIC + "%");
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps, conn);
		}
		return 0;
	}

	@Override
	public List<CoachStaffEntity> listAll(String cn, String cIC, Page page) {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM t_coach_staff where delete_logo='N'";
		if (cn != null && cn.trim() != "") {
			sql = sql + " and coach_name =?";
		}
		if (cIC != null && cIC.trim() != "") {
			sql = sql + " and coach_id_card like ?";
		}
		sql = sql + "limit ?,?";
		System.out.println("sql" + sql);
		List<CoachStaffEntity> list = new ArrayList<CoachStaffEntity>();
		try {
			ps = conn.prepareStatement(sql);
			int j = 1;
			if (cn != null && cn.trim() != "") {
				ps.setString(j++, cn);
			}
			if (cIC != null && cIC.trim() != "") {
				ps.setString(j++, "%" + cIC + "%");
			}
			ps.setInt(j++, (page.getCurrentPage() - 1) * page.getRowSize());
			ps.setInt(j++, page.getRowSize());
			rs = ps.executeQuery();
			while (rs.next()) {
				int i = 1;
				Long id = rs.getLong(i++);
				String drivingSchoolName = rs.getString(i++);
				String coachName = rs.getString(i++);
				String coachNumber = rs.getString(i++);
				String coachIdCard = rs.getString(i++);
				String coachSex = rs.getString(i++);
				String coachDateBirth = rs.getString(i++);
				String nation = rs.getString(i++);
				String quasiDrivingType = rs.getString(i++);
				String mustTeachModels = rs.getString(i++);
				String placeOfDomicile = rs.getString(i++);
				String phone = rs.getString(i++);
				String referenceStatus = rs.getString(i++);
				String returnMessage = rs.getString(i++);
				String popedom = rs.getString(i++);
				String province = rs.getString(i++);
				String city = rs.getString(i++);
				String contactAddress = rs.getString(i++);
				String mustTeachClasses = rs.getString(i++);
				String learnHistory = rs.getString(i++);
				String socialSecurityNumber = rs.getString(i++);
				Date drivingLicenceStatsTime = rs.getDate(i++);
				Date pactStatsTime = rs.getDate(i++);
				Date pactEndTime = rs.getTimestamp(i++);
				String workingState = rs.getString(i++);
				Date certificateTime = rs.getDate(i++);
				Date validTime = rs.getTimestamp(i++);
				String deleteLogo = rs.getString(i++);
				String learnCardNumber = rs.getString(i++);
				Date createTime=rs.getTimestamp(i++);
				Date updateTime=rs.getTimestamp(i++);
				String createName=rs.getString(i++);
				String updateName=rs.getString(i++);
				list.add(new CoachStaffEntity(id, drivingSchoolName, coachName, coachNumber, coachIdCard, coachSex,
						coachDateBirth, nation, quasiDrivingType, mustTeachModels, placeOfDomicile, phone,
						referenceStatus, returnMessage, popedom, province, city, contactAddress, mustTeachClasses,
						learnHistory, socialSecurityNumber, drivingLicenceStatsTime, pactStatsTime, pactEndTime,
						workingState, certificateTime, validTime,deleteLogo,learnCardNumber,createTime,updateTime,createName,updateName));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

}
